﻿/*
=============================================================================
Author:魏宝辉通过CodeSmithe生成
CreatedTime:2012-10-31
Description:三层结构数据访问层的通用数据库访问方法
=============================================================================
*/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Text.RegularExpressions;
using System.Web;
using System.Collections;
namespace FTchina.DAL
{
	///<summary>
	/// 数据库访问通用方法类
	/// </summary>
	public static class DBHelper
	{
		private static SqlConnection connection;//连接对象
		public static SqlConnection Connection
		{
			get 
			{
				//web.config  <configuration> 节点下添加配置
				//<connectionStrings>
				//<add name="default" connectionString="server=.;database=ExtFTchina;uid=sa;pwd=sa;MultipleActiveResultSets=True;" providerName="SqlServer"/>
				//</connectionStrings>
				string connectionString =System.Configuration.ConfigurationManager.ConnectionStrings["default"].ConnectionString;
				
				if (connection == null)
				{
					connection = new SqlConnection(connectionString);
					connection.Open();
					}
				else if (connection.State == System.Data.ConnectionState.Closed)
				{
					connection.Open();
				}
				else if (connection.State == System.Data.ConnectionState.Broken)
				{
					connection.Close();
					connection.Open();
				}
				return connection;
			}
		}
		
        //执行SQl语句
        public static int ExecuteCommand(string safeSql)
        {
            int result = 0;
            using (SqlCommand cmd = new SqlCommand(safeSql, Connection))
            {
                try
                {
                     result = cmd.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                     FTchina.Library.LogHelper.Error(e.Message);
                }
                
                return result;
            }
        }
        /// <summary> 
        /// 执行Sql查询语句 
        /// </summary> 
        /// <param name="sqlstr">传入的Sql语句</param> 
        public static bool ExecuteSql(string sqlstr)
        {
            try
            {
                ExecuteCommand(sqlstr);
                return true;
            }
            catch (Exception e)
            {
                 FTchina.Library.LogHelper.Error(sqlstr + e.Message);
                return false;
            }
        }



        /// <summary>
        /// 在事务中处理大量数据
        /// </summary>
        /// <param name="sqls"></param>
        public static Boolean ExecuteSqlTransaction(ArrayList sqls)
        {

            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = Connection;
                SqlTransaction transaction = Connection.BeginTransaction();
                cmd.Transaction = transaction;
                try
                {

                    for (int i = 0; i < sqls.Count; i++)
                    {
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = sqls[i].ToString();
                        cmd.ExecuteNonQuery();
                    }
                    transaction.Commit();
                    return true;

                }
                catch (System.Exception er)
                {
                     FTchina.Library.LogHelper.Error(er.Message);

                    transaction.Rollback();
                    return false;
                }
            }
        }





        //带参数值的sql
        public static int ExecuteCommand(string sql, params SqlParameter[] values)
        {
            using (SqlCommand cmd = new SqlCommand(sql, Connection))
            {
                cmd.Parameters.AddRange(values);
                return cmd.ExecuteNonQuery();
            }
        }
        //执行SQl语句的
        public static int GetScalar(string safeSql)
        {
            using (SqlCommand cmd = new SqlCommand(safeSql, Connection))
            {
                int result = Convert.ToInt32(cmd.ExecuteScalar());
                return result;
            }
        }

        public static int GetScalar(string sql, params SqlParameter[] values)
        {
            using (SqlCommand cmd = new SqlCommand(sql, Connection))
            {
                cmd.Parameters.AddRange(values);
                int result = Convert.ToInt32(cmd.ExecuteScalar());
                return result;
            }
        }



        /// <summary>
        /// 获取只包含架构信息的表
        /// </summary>
        /// <param name="sqlstr"></param>
        /// <returns></returns>
        public static DataTable GetSchemaTable(string safeSql)
        {
            DataTable dt = new DataTable();
            using (SqlCommand cmd = new SqlCommand(safeSql, Connection))
            {
                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                dt = reader.GetSchemaTable();
                reader.Close();
                return dt;
            }
        }


        //查询返回记录集对象
        public static SqlDataReader GetReader(string safeSql)
        {
            using (SqlCommand cmd = new SqlCommand(safeSql, Connection))
            {
                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return reader;
            }
        }
        //带参数值的查询返回记录集对象
        public static SqlDataReader GetReader(string sql, params SqlParameter[] values)
        {
            using (SqlCommand cmd = new SqlCommand(sql, Connection))
            {
                cmd.Parameters.AddRange(values);
                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return reader;
            }
        }
        //返回表格的查询
        public static DataSet GetDataSet(string safeSql)
        {
            DataSet ds = new DataSet();
            using (SqlCommand cmd = new SqlCommand(safeSql, Connection))
            {
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    try
                    {
                        da.Fill(ds);
                    }
                    catch (Exception e)
                    {
                         FTchina.Library.LogHelper.Error(e.Message);
                    }
                    return ds;
                }
            }
        }



        /// <summary>
        /// 带分页dataset
        /// </summary>
        /// <param name="safeSql"></param>
        /// <param name="startindex"></param>
        /// <param name="pagesize"></param>
        /// <param name="tablename"></param>
        /// <returns></returns>
        public static DataSet ExecuteSqlForDataSetPageing(string safeSql, int startindex, int pagesize, string tablename)
        {
            DataSet ds = new DataSet();
            using (SqlCommand cmd = new SqlCommand(safeSql, Connection))
            {
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    try
                    {
                        da.Fill(ds, startindex, pagesize, tablename);
                    }
                    catch (Exception e)
                    {
                         FTchina.Library.LogHelper.Error(e.Message);
                    }
                    
                    return ds;
                }
            }
        }


        //带参数的查询返回表
        public static DataSet GetDataSet(string sql, params SqlParameter[] values)
        {
            DataSet ds = new DataSet();
            using (SqlCommand cmd = new SqlCommand(sql, Connection))
            {
                cmd.Parameters.AddRange(values);
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    try
                    {
                        da.Fill(ds);
                    }
                    catch (Exception e)
                    {
                         FTchina.Library.LogHelper.Error(e.Message);
                    }
                    return ds;
                }
            }
        }

        /// <summary> 
        /// 返回指定Sql语句的DataTable 
        /// </summary> 
        /// <param name="sqlstr">传入的Sql语句</param> 
        /// <returns>DataTable</returns> 
        public static DataTable GetDataTable(string sqlstr)
        {
            return GetDataSet(sqlstr).Tables[0];

        }
        /// <summary> 
        /// 返回指定Sql语句的DataTable 
        /// </summary> 
        /// <param name="sqlstr">传入的Sql语句</param> 
        /// <returns>DataTable</returns> 
        public static DataTable GetDataTable(string sqlstr, params SqlParameter[] values)
        {
            return GetDataSet(sqlstr, values).Tables[0];

        }



        /// <summary> 
        /// 返回指定Sql语句的DataTable，带缓存
        /// </summary> 
        /// <param name="sqlstr">传入的Sql语句</param> 
        /// <param name="cache">cache标志</param> 
        /// <returns>DataTable</returns> 
        public static DataTable GetDataTable(string sqlstr, string cache)
        {
            if (cache.Length != 0)
            {
                if (FTchina.Caching.FTchinaCache.IsExits(cache + sqlstr))
                {
                    return (DataTable)HttpRuntime.Cache.Get(cache + sqlstr);
                }
                else
                {
                    DataTable dt = GetDataSet(sqlstr).Tables[0];
                    HttpRuntime.Cache.Insert(cache + sqlstr, dt);
                    return dt;
                }

            }
            else
            {
                return GetDataSet(sqlstr).Tables[0];
            }
        }
		//检测数据是否存在，返回真假
        public static bool Testread(string sqlstr, params SqlParameter[] values)
        {
            bool flag = true;
            DataSet ds = GetDataSet(sqlstr,values);
            if (ds.Tables.Count == 0) { flag = false; }
            else
            {
                if (ds.Tables[0].Rows.Count > 0) flag = true;
                else flag = false;
            }
            // FTchina.Library.LogHelper.Error(sqlstr);
            return flag;
        }
        //检测数据是否存在，返回真假
        public static bool Testread(string sqlstr)
        {
            bool flag = true;
            DataSet ds = GetDataSet(sqlstr);
            if (ds.Tables.Count == 0) { flag = false; }
            else
            {
                if (ds.Tables[0].Rows.Count > 0) flag = true;
                else flag = false;
            }
            //  FTchina.Library.LogHelper.Error(sqlstr);
            return flag;
        }


        /// <summary>
        /// 返回特定值的查询结果
        /// </summary>
        /// <param name="wKey">列名称 如 "select com from table where id=3 "中的com</param>
        /// <param name="fTable">表名称 如 "select com from table"中的table</param>
        /// <param name="wStr">限制条件 如 "select com from table where id=3 "中的id=3</param>
        /// <returns></returns>
        public static string GetValueByKey(string wKey, string fTable, string wStr)
        {
            try
            {
                return GetDataTable("select " + wKey + " from " + fTable + " where " + wStr).Rows[0][0].ToString().Trim();
            }
            catch (Exception er)
            {
                 FTchina.Library.LogHelper.Error(wStr + fTable + "  " + er.Message);

                return "__";
            }
        }
        /// <summary>
        /// 返回特定值的查询结果，带缓存
        /// </summary>
        /// <param name="wKey">列名称 如 "select com from table where id=3 "中的com</param>
        /// <param name="fTable">表名称 如 "select com from table"中的table</param>
        /// <param name="wStr">限制条件 如 "select com from table where id=3 "中的id=3</param>
        /// <param name="Cache">Cache标志</param>
        /// <returns></returns>
        public static string GetValueByKey(string wKey, string fTable, string wStr, string Cache)
        {
            string tempwords = string.Empty;

            if (Cache.Length != 0)
            {
                if (FTchina.Caching.FTchinaCache.IsExits(Cache + wKey + fTable + wStr))
                {
                    tempwords = FTchina.Caching.FTchinaCache.GetCache(Cache + wKey + fTable + wStr);
                }
                else
                {
                    try
                    {
                        tempwords = GetDataTable("select " + wKey + " from " + fTable + " where " + wStr).Rows[0][0].ToString();
                        FTchina.Caching.FTchinaCache.AddCache(Cache + wKey + fTable + wStr, tempwords);
                    }
                    catch
                    {

                        tempwords = "";
                    }
                }

            }
            else
            {
                try
                {
                    tempwords = GetDataTable("select " + wKey + " from " + fTable + " where " + wStr).Rows[0][0].ToString();
                }
                catch
                {

                    tempwords = "";
                }

            }
            return tempwords;
        }




        /// <summary>
        /// 执行T-SQL语句
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public static DataTable[] Query(string query)
        {
            // Use ADO.NET for doing raw queries
            // Parse at each "go" statement and execute each batch independently

            ArrayList result = new ArrayList();

            try
            {


                // Tack on whitespace so that the RegEx doesn't mess up
                query += "\r\n";

                // Split query at each "go"
                Regex regex = new Regex("[\r\n][gG][oO][\r\n]");

                MatchCollection matches = regex.Matches(query);

                int prevIndex = 0;
                string tquery;

                for (int i = 0; i < matches.Count; i++)
                {
                    Match m = matches[i];

                    tquery = query.Substring(prevIndex, m.Index - prevIndex);

                    if (tquery.Trim().Length > 0)
                    {
                        SqlDataAdapter myCommand = new SqlDataAdapter(tquery.Trim(), connection);

                        DataSet singleresult = new DataSet();
                        myCommand.Fill(singleresult);

                        for (int j = 0; j < singleresult.Tables.Count; j++)
                        {
                            result.Add(singleresult.Tables[j]);
                        }
                    }

                    prevIndex = m.Index + 3;
                }

                tquery = query.Substring(prevIndex, query.Length - prevIndex);

                if (tquery.Trim().Length > 0)
                {
                    SqlDataAdapter myCommand = new SqlDataAdapter(tquery.Trim(), connection);

                    DataSet singleresult = new DataSet();
                    myCommand.Fill(singleresult);

                    for (int j = 0; j < singleresult.Tables.Count; j++)
                    {
                        result.Add(singleresult.Tables[j]);
                    }
                }
            }
            catch (Exception e)
            {
                 FTchina.Library.LogHelper.Error(e.Message);
            }

            return (DataTable[])result.ToArray(typeof(DataTable));

        }



        #region 分页的方法page为当前页码，NUMCount为每页数据
        public static DataSet ShowForPage(string table, string Where, int curragePage, int NUMCount)
        {
            int number = 0;
            if (curragePage > 1)
            {
                number = NUMCount * (curragePage - 1);
            }

            //  string strsql = "SELECT TOP " + NUMCount + " * FROM " + table + " WHERE id NOT IN (SELECT TOP " + number + " id FROM " + table + " ORDER BY id desc) and (" + Where + ") ORDER BY id desc";
            string strsql = "SELECT TOP " + NUMCount + " * FROM " + table + " WHERE id NOT IN (SELECT TOP " + number + " id FROM " + table + " where (" + Where + ") ORDER BY id desc) and (" + Where + ") ORDER BY id desc";
            return GetDataSet(strsql);
        }
        #endregion
        

	}
}
